import common
import MySQLdb

db = MySQLdb.connect(host="localhost", port=3306, user="root",
                     passwd="123456", db="quant_data", charset='utf8')
cursor=db.cursor()

#f = fopen('all.csv','r')
ids = common.getSecurityIDs(db)


with open('spiders/all-2013.csv') as f:
    content = f.readlines()
    n = len(content)
    for i in range(n):
        
        s = content[i].replace('\n','').split(',')
        ticker = s[0]
        yr = s[1]        
        table = 'balance_sheet_stox'
        security_id = ids[ticker][0]        
        
        print '%d / %d , %s (%d) year %s ...' %(i,n,ticker,security_id, yr)
        
        if(ids[ticker][2]==12): #is bank
            table = 'balance_sheet_bank_stox'           
            
        template_sql = """UPDATE `%s` SET `investment_capital_owners_equity`=%f
                    WHERE `security_id` = '%d' AND `year`='%s' AND `quarter`='%d'
                    """
        q = 4
        for i in range(2,6):
            if s[i]:
                value = float(s[i])/1000
                if(yr=='2013'):
                    q = 1                    
                sql =   template_sql %(table,value,security_id,yr, q)                
                common.fetchSQL(db,sql)             
            q = q - 1;                
        
cursor.close()
db.commit()
db.close()    